﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Word = Microsoft.Office.Interop.Word;
using System.Reflection;
using System.Data.Odbc;
using System.Data.OleDb;
using System.IO;

namespace MailMerge_Generator
{
    public partial class Frm_Main : Form
    {
        //************************
        //****Public Variables****
        //************************
        public static LetterObject oLetter = new LetterObject();
        public static String DataFieldText;
        public static object oMissing = System.Reflection.Missing.Value;
        public static object oEndOfDoc = "\\endofdoc";
        public static Word._Application oWordApp;
        public static Word._Document oDoc;
        public object oTrue = true;
        public object oFalse = false;
        public Frm_Main()
        {
            InitializeComponent();
            txt_MergeListPath.Text = "e.g. MailMerge_Cash.xlsx";
            txt_RoyaltyListPath.Text = "e.g. RoyaltiesbySKU.xlsx";
        }
        private String wikiURL = Properties.Settings.Default.HelpURL;
        //****************************
        //**Public Methods/Functions**
        //****************************
        public String CreateDataField()
        {
            String DbText;
            String DbDSwitch;
            String DbCSwitch;
            String DbSSwitch;
            String SQLWhere;
            String DSFullName;
            DSFullName = oLetter.TableFilePath;
            DSFullName = DSFullName.Replace(@"\", @"\\");
            DbDSwitch = @" \d " + (Char)34 + DSFullName + (Char)34;
            DbCSwitch = @" \c " + (Char)34 + (String)Utilities.GetConnectionString(DSFullName, true) + (Char)34;
            SQLWhere = "WHERE ((" + (Char)96 + oLetter.TableKey + (Char)96 + " = " + (Char)39 + "@@" + (Char)39 + "))";
            DbSSwitch = " \\s " + (Char)34 + MakeSQL() + " " + SQLWhere + (Char)34;
            DbText = DbDSwitch + DbCSwitch + DbSSwitch + " \\l " + (Char)34 + "25" + (Char)34 + " \\b " + (Char)34 + "181" + (Char)34 + " \\h";
            return DbText;
        }
        public String MakeSQL()
        {
            String sqlLine = "SELECT ";
            for (int i = 0; i < oLetter.TableFields.Count; i++)
            {
                sqlLine += (Char)96 + oLetter.TableFields[i] + (Char)96;
                if (i < oLetter.TableFields.Count - 1)
                {
                    sqlLine += ", ";
                }
            }
            sqlLine += " FROM " + (Char)96 + oLetter.TableSource + (Char)96;
            //MessageBox.Show(sqlLine);
            return sqlLine;
        }
        public void SetupMailMerge()
        {
            Word.MailMerge oMailMerge;
            oMailMerge = oDoc.MailMerge;
            object MergeSQL = "SELECT * FROM " + (Char)96 + oLetter.MergeSource + (Char)96;
            object oConn = Utilities.GetConnectionString(oLetter.MergeFilePath, false);
            try
            {
                oMailMerge.OpenDataSource(Name: oLetter.MergeFilePath, Connection: oConn, SQLStatement: MergeSQL);
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                //Throwing the error results in an error window and the mail merge not working.
                throw;
            }
        }
        public void AddTable()
        {
            Word.Paragraph oPara2;
            object oRng;
            if (oLetter.bk_HasTable && oDoc.Bookmarks.Exists(oLetter.bk_Table))
            {
                oRng = oDoc.Bookmarks.get_Item((object)oLetter.bk_Table).Range;
            }
            else
            {
                oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            }
            oPara2 = oDoc.Content.Paragraphs.Add(ref oRng);
            Word.Field oDataField;
            oDataField = oPara2.Range.Fields.Add(oPara2.Range, Word.WdFieldType.wdFieldDatabase, DataFieldText, false);
            oDataField.ShowCodes = true;
            if (oLetter.HasMerge)
            {
                oWordApp.Selection.Find.Replacement.Text = "";
                oWordApp.Selection.Find.Forward = true;
                oWordApp.Selection.Find.Wrap = Word.WdFindWrap.wdFindStop;
                oWordApp.Selection.Find.Text = "@@";
                oWordApp.Selection.Find.Execute();
                oDoc.Fields.Add(oWordApp.Selection.Range, Word.WdFieldType.wdFieldMergeField, oLetter.MergeKey, false);
            }
            oDataField.ShowCodes = false;
            oPara2.Range.InsertParagraphAfter();
        }
        public void AddParagraph(LetterObject.paragraph o)
        {
            object oRng;
            Word.Paragraph oPara;
            if (o.hasMark && oDoc.Bookmarks.Exists(o.bookmark))
            {
                oRng = oDoc.Bookmarks.get_Item((object)o.bookmark).Range;
            }
            else
            {
                oRng = oDoc.Bookmarks.get_Item(ref oEndOfDoc).Range;
            }
            oPara = oDoc.Content.Paragraphs.Add(ref oRng);
            oPara.Range.Text = o.text;
            oPara.Format.SpaceBefore = o.SpaceAbove;
            oPara.Format.SpaceAfter = o.SpaceBelow;
            oPara.Format.LeftIndent = o.LeftIndent;
            oPara.Format.FirstLineIndent = o.FirstLine;
            oPara.Range.InsertParagraphAfter();
        }
        public void AddMergeFields(String mergefield)
        {
            oWordApp.Selection.Find.Replacement.Text = "";
            oWordApp.Selection.Find.Forward = true;
            oWordApp.Selection.Find.Wrap = Word.WdFindWrap.wdFindStop;
            oWordApp.Selection.Find.Text = "@" + mergefield;
            oWordApp.Selection.Find.Execute();
            oDoc.Fields.Add(oWordApp.Selection.Range, Word.WdFieldType.wdFieldMergeField, mergefield, false);
        }
        public void GenerateLetter()
        {
            //open Word if it isn't already open.
            try
            {
                oWordApp = (Word.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Word.Application");
            }
            catch (System.Runtime.InteropServices.COMException)
            {
                oWordApp = new Word.Application();
            }
            //create a new word document
            oDoc = oWordApp.Documents.Add(ref oMissing, ref oMissing, ref oMissing, ref oMissing);
            oDoc.ActiveWindow.View.Type = Word.WdViewType.wdPrintView;
            //oDoc.ActiveWindow.View.Type = Word.WdViewType.wdNormalView;
            if (oLetter.HasMerge)
            {
                SetupMailMerge();
            }
            //*********************
            //*****Letter body*****
            //*********************

            if (oLetter.Greeting.exists)
            {
                AddParagraph(oLetter.Greeting);
            }
            if (oLetter.Body.exists)
            {
                AddParagraph(oLetter.Body);
            }
            if (oLetter.HasTable)
            {
                DataFieldText = CreateDataField();
                //Add database field text.
                AddTable();
            }
            if (oLetter.Summary.exists)
            {
                AddParagraph(oLetter.Summary);
            }
            if (oLetter.Closing.exists)
            {
                AddParagraph(oLetter.Closing);
                AddParagraph(oLetter.Sender);
            }
            //For each item in the list of mergefields, run the method above which searches for and replaces them with mergefields.
            if (oLetter.HasMerge)
            {
                for (int i = 0; i < oLetter.MergeFields.Count; i++)
                {
                    oWordApp.Selection.GoTo(Word.WdGoToItem.wdGoToLine, Word.WdGoToDirection.wdGoToFirst);
                    if (oWordApp.Selection.Find.Execute((object)"@" + oLetter.MergeFields[i]))
                    {
                        Word.Range oFound = oDoc.Range(oWordApp.Selection.Range.Start, oWordApp.Selection.Range.End);
                        oFound.Select();
                        AddMergeFields(oLetter.MergeFields[i]);
                    }
                }
            }
            oDoc.Fields.Update();
            oWordApp.Visible = true;
        }
        private void BoxChecked(object sender, EventArgs e)
        {
            CheckBox chk = (CheckBox)sender;
            switch (chk.Name)
            {
                case "chk_Greeting":
                    oLetter.Greeting.exists = chk.Checked;
                    rtf_ltGreeting.Enabled = chk.Checked;
                    break;
                case "chk_Body":
                    oLetter.Body.exists = chk.Checked;
                    rtf_ltBody.Enabled = chk.Checked;
                    break;
                case "chk_Summary":
                    oLetter.Summary.exists = chk.Checked;
                    rtf_ltSummary.Enabled = chk.Checked;
                    break;
                case "chk_Closing":
                    oLetter.Closing.exists = chk.Checked;
                    oLetter.Sender.exists = chk.Checked;
                    txt_ltClosing.Enabled = chk.Checked;
                    txt_ltSignature.Enabled = chk.Checked;
                    break;
                case "chk_Table":
                    oLetter.HasTable = chk.Checked;
                    txt_RoyaltyListPath.Enabled = chk.Checked;
                    btn_SelectRoyaltyList.Enabled = chk.Checked;
                    if (chk.Checked)
                    {
                        lbl_Table.ForeColor = Color.Black;
                    }
                    else
                    {
                        lbl_Table.ForeColor = Color.DarkRed;
                    }
                    break;
                case "chk_Merge":
                    oLetter.HasMerge = chk.Checked;
                    txt_MergeListPath.Enabled = chk.Checked;
                    btn_SelectMergeList.Enabled = chk.Checked;
                    if (chk.Checked)
                    {
                        lbl_MailMerge.ForeColor = Color.Black; 
                    } else
                    {
                        lbl_MailMerge.ForeColor = Color.DarkRed;
                    }
                    break;
                default:
                    break;
            }
        }
        //**********************
        //*****Menu Methods*****
        //**********************
        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            oDoc = null;
            oWordApp = null;
            Close();
        }
        private void aboutToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Frm_About oAbout = new Frm_About();
            oAbout.Show();
        }
        private void clearToolStripMenuItem_Click(object sender, EventArgs e)
        {
            oLetter.Clear();
            txt_MergeListPath.Text = "";
            txt_RoyaltyListPath.Text = "";
            rtf_ltGreeting.Text = "";
            rtf_ltBody.Text = "";
            rtf_ltSummary.Text = "";
            txt_ltClosing.Text = "";
            txt_ltSignature.Text = "";
        }
        private void saveToolStripMenuItem_Click(object sender, EventArgs e)
        {
            oLetter.MergeFilePath = txt_MergeListPath.Text;
            oLetter.TableFilePath = txt_RoyaltyListPath.Text;
            oLetter.Greeting.text = rtf_ltGreeting.Text;
            oLetter.Body.text = rtf_ltBody.Text;
            oLetter.Summary.text = rtf_ltSummary.Text;
            oLetter.Closing.text = txt_ltClosing.Text;
            oLetter.Sender.text = txt_ltSignature.Text;
            try
            {
                //xml serialization and output to a file somewhere
                SaveFileDialog sfd = new SaveFileDialog();
                sfd.Title = "Save Mail Merge Template";
                sfd.Filter = "Templates (*.xml)|";
                sfd.DefaultExt = "xml";
                sfd.FilterIndex = 1;
                sfd.RestoreDirectory = true;
                if (sfd.ShowDialog() == DialogResult.OK)
                {
                    System.Xml.Serialization.XmlSerializer writer = new System.Xml.Serialization.XmlSerializer(typeof(LetterObject));
                    System.IO.StreamWriter file = new System.IO.StreamWriter(sfd.FileName);
                    writer.Serialize(file, oLetter);
                    file.Close();
                }
                else
                {
                    //Should there be a pop-up here?
                }

            }
            catch (Exception er)
            {
                MessageBox.Show("An error occurred:" + (Char)10 + er);
                throw;
            }
        }
        private void loadToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Title = "Open Mail Merge Template";
                ofd.Filter = "Templates (*.xml)|";
                ofd.DefaultExt = "xml";
                ofd.FilterIndex = 1;
                ofd.RestoreDirectory = true;
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    oLetter.Clear();
                    System.Xml.Serialization.XmlSerializer reader = new System.Xml.Serialization.XmlSerializer(typeof(LetterObject));
                    System.IO.StreamReader file = new System.IO.StreamReader(ofd.FileName);
                    oLetter = (LetterObject)reader.Deserialize(file);
                    file.Close();
                }
                else
                {
                    //Anything?
                }
                //File paths
                txt_MergeListPath.Text = oLetter.MergeFilePath;
                txt_RoyaltyListPath.Text = oLetter.TableFilePath;
                //Letter parts
                rtf_ltGreeting.Text = oLetter.Greeting.text;
                rtf_ltBody.Text = oLetter.Body.text;
                rtf_ltSummary.Text = oLetter.Summary.text;
                txt_ltClosing.Text = oLetter.Closing.text;
                txt_ltSignature.Text = oLetter.Sender.text;
                //Checkboxes
                chk_Greeting.Checked = oLetter.Greeting.exists;
                chk_Body.Checked = oLetter.Body.exists;
                chk_Summary.Checked = oLetter.Summary.exists;
                chk_Closing.Checked = oLetter.Closing.exists;
            }
            catch (Exception er)
            {
                MessageBox.Show("An error occurred:" + (Char)10 + er);
                throw;
            }
        }
        private void openconfigToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Frm_Config1 cfm = new Frm_Config1();
            cfm.Show();
        }
        private void letterDetailsToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Form oConfig2 = new Frm_Config2();
            oConfig2.Show();
        }
        private void previewToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //This is something I am not trying to figure out how to do.
            //Originally this just showed a popup with the sql for the table.
            //This was more for testing than anything else.
            //A potential future is a window with the letter kind of laid out.
            MessageBox.Show(MakeSQL());
        }
        private void makeLetterToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //Make sure to grab the text.
            oLetter.Body.text = rtf_ltBody.Text;
            oLetter.Summary.text = rtf_ltSummary.Text;
            oLetter.Closing.text = txt_ltClosing.Text;
            oLetter.Sender.text = txt_ltSignature.Text;
            try
            {
                //Check to see if all of the required fields are filled with data. Protect the user from themselves.
                if ((oLetter.HasTable && oLetter.TableKey.Length < 1) || (oLetter.HasMerge && oLetter.MergeKey.Length < 1))
                {
                    throw new Exception<Frm_Main>((Char)10 + "Some required information is not entered." + (Char)10 + "Please make sure that all expected information is entered and correct." + (Char)10 + "You will not be able to proceed until you enter all required information.");
                }
                if((oLetter.HasTable && oLetter.TableFields.Count < 1) || (oLetter.HasMerge && oLetter.MergeFields.Count < 1))
                {
                    throw new Exception<Frm_Main>((Char)10 + "Some required information is not entered." + (Char)10 + "Please make sure that all expected information is entered and correct." + (Char)10 + "You will not be able to proceed until you enter all required information.");
                }
                if (oLetter.HasMerge && !File.Exists(oLetter.MergeFilePath))
                {
                    oLetter.HasMerge = false;
                }
                if (oLetter.HasTable && !File.Exists(oLetter.TableFilePath))
                {
                    oLetter.HasTable = false;
                }
                if (oLetter.HasTemplate && !File.Exists(oLetter.TemplateFilePath))
                {
                    oLetter.HasTemplate = false;
                }
                GenerateLetter();
            }
            catch (Exception er)
            {
                MessageBox.Show("An error occurred:" + (Char)10 + er);
                Console.WriteLine(er);
                if (oDoc != null)
                {
                    oDoc.Close(Word.WdSaveOptions.wdDoNotSaveChanges);
                    if (oWordApp != null)
                    {
                        oWordApp.Quit();
                    }
                    oDoc = null;
                }
            }

        }
        private void formattingConfigurationToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Frm_Config3 frm = new Frm_Config3();
            frm.Show();
        }
        private void licenseToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Frm_License frm = new Frm_License();
            frm.Show();
        }
        private void howDoIToolStripMenuItem_Click(object sender, EventArgs e)
        {
            try
            {
                System.Diagnostics.Process.Start(wikiURL);
            }
            catch (System.ComponentModel.Win32Exception noBrowser)
            {
                if (noBrowser.ErrorCode == -2147467259) MessageBox.Show(noBrowser.Message);
            }
            catch (System.Exception other)
            {
                MessageBox.Show("An unknown error occurred." + (Char)10 + "To view the help page, please copy this url into your web browser:" + (Char)10 + wikiURL);
            }
        }
        //************************
        //*****Button Methods*****
        //************************
        private void btn_SelectMergeList_Click(object sender, EventArgs e)
        {
            txt_MergeListPath.Text = Utilities.FindFile("Mail Merge Source");
        }
        private void btn_SelectRoyaltyList_Click(object sender, EventArgs e)
        {
            txt_RoyaltyListPath.Text = Utilities.FindFile("Royalty List");
        }
        //*************************
        //***Text change Methods***
        //*************************
        private void txt_MergeListPath_TextChanged(object sender, EventArgs e)
        {
            if (File.Exists(txt_MergeListPath.Text))
            {
                oLetter.MergeFilePath = txt_MergeListPath.Text;
                oLetter.MergeSource = Utilities.FindSheetName(oLetter.MergeFilePath);
            }
        }
        private void txt_RoyaltyListPath_TextChanged(object sender, EventArgs e)
        {
            if (File.Exists(txt_RoyaltyListPath.Text))
            {
                oLetter.TableFilePath = txt_RoyaltyListPath.Text;
                oLetter.TableSource = Utilities.FindSheetName(oLetter.TableFilePath);
            }
        }
        private void rtf_ltBody_Leave(object sender, EventArgs e)
        {
            oLetter.Body.text = rtf_ltBody.Text;
        }
        private void rtf_ltSummary_Leave(object sender, EventArgs e)
        {
            oLetter.Summary.text = rtf_ltSummary.Text;
        }
        private void rtf_ltClosing_Leave(object sender, EventArgs e)
        {
            oLetter.Closing.text = txt_ltClosing.Text;
        }
        private void rtf_ltSignature_Leave(object sender, EventArgs e)
        {
            oLetter.Sender.text = txt_ltSignature.Text;
        }
        private void rtf_ltGreeting_Leave(object sender, EventArgs e)
        {
            oLetter.Greeting.text = rtf_ltGreeting.Text;
        }
    }
}
